package net.hekka.canyoutalk.data.executor;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import net.hekka.canyoutalk.data.store.PhoneBookSqlStore;
import net.hekka.canyoutalk.data.vo.PhoneBookVo;
import net.hekka.canyoutalk.data.vo.list.PhoneBookVoList;
import net.hekka.canyoutalk.share.database.SqlExecutor;

/**
 * phonebook 테이블에 관련된 SQL 실행에 대한 클래스이다.
 * @author Jeffrey Ha
 */
public class PhoneBookSqlExecutor extends SqlExecutor {

    /**
     * phonebook 테이블에 새로운 데이터를 insert한다.
     * @param dbConnection 데이터베이스 Connection
     * @param phoneBookVo PhoneBookVo
     * @return 입력된 갯수
     * @throws SQLException SQL 작업중 오류 발생 시
     */
    public int insertPhoneBook(Connection dbConnection, PhoneBookVo phoneBookVo) throws SQLException {
        int count = 0;
        try {
            //set Database connection
            setDbConnection(dbConnection);
            //set DatabaseType
            setDatabaseType();
            //get sql statement
            String sql = PhoneBookSqlStore.getPhoneBookInsertSql(getDatabaseType());
            //set preparedstatement
            setPreparedStatement(sql);
            //set parameter of statement
            setPhoneBookInsertParameter(phoneBookVo);
            //execute Sql
            count = getPreparedStatement().executeUpdate();
        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex) {
            }
        }
        return count;
    }

    private void setPhoneBookInsertParameter(PhoneBookVo phoneBookVo) throws SQLException {
        int sequence = 1;

        // 전화번호
        this.setStringPreparedStatement(sequence++, phoneBookVo.getNumber());
        // 이름
        this.setStringPreparedStatement(sequence++, phoneBookVo.getName());
    }

    /**
     * phonebook 테이블에 데이터를 update 한다.
     * @param dbConnection 데이터베이스 Connection
     * @param phoneBookVo PhoneBookVo
     * @return 수정된 갯수
     * @throws SQLException SQL 작업중 오류 발생 시
     */
    public int updatePhoneBook(Connection dbConnection, PhoneBookVo phoneBookVo) throws SQLException {
        int count = 0;
        try {
            //set Database connection
            setDbConnection(dbConnection);
            //set DatabaseType
            setDatabaseType();
            //get sql statement
            String sql = PhoneBookSqlStore.getPhoneBookUpdateSql(getDatabaseType());
            //set preparedstatement
            setPreparedStatement(sql);
            //set parameter of statement
            setPhoneBookUpdateParameter(phoneBookVo);
            //execute Sql
            count = getPreparedStatement().executeUpdate();
        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex) {
            }
        }
        return count;
    }

    private void setPhoneBookUpdateParameter(PhoneBookVo phoneBookVo) throws SQLException {
        int sequence = 1;

        // 이름
        this.setStringPreparedStatement(sequence++, phoneBookVo.getName());
        // 전화번호
        this.setStringPreparedStatement(sequence++, phoneBookVo.getNumber());

    }

    /**
     * phonebook 테이블에 데이터를 delete 한다.
     * @param dbConnection 데이터베이스 Connection
     * @param phoneBookVo PhoneBookVo
     * @return 삭제된 갯수
     * @throws SQLException SQL 작업중 오류 발생 시
     */
    public int deletePhoneBook(Connection dbConnection, String number) throws SQLException {
        int count = 0;
        try {
            //set Database connection
            setDbConnection(dbConnection);
            //set DatabaseType
            setDatabaseType();
            //get sql statement
            String sql = PhoneBookSqlStore.getPhoneBookDeleteSql(getDatabaseType());
            //set preparedstatement
            setPreparedStatement(sql);
            //set parameter of statement
            // 전화번호
            this.setStringPreparedStatement(1, number);

            //execute Sql
            count = getPreparedStatement().executeUpdate();
        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex) {
            }
        }
        return count;
    }

    /**
     * phonebook의 데이터수를 구한다.
     * @param dbConnection 데이터베이스 Connection
     * @return phonebook의 데이터수
     * @throws SQLException SQL문 실행 중 오류 발생 시
     */
    public int getCountOfPhoneBook( Connection dbConnection ) throws SQLException {
        ResultSet rs = null;
        int totalCount = 0;
        try {
            // set Database Connection
            setDbConnection(dbConnection);
            // set Database Type
            setDatabaseType();
            // get sql statement
            String sql = PhoneBookSqlStore.getCountOfPhoneBookSelectSql(getDatabaseType());
            // set PrepareStatement
            setPreparedStatement(sql);
            // set parameters of Preparestatement

            // execute sql
            rs = getPreparedStatement().executeQuery();
            while (rs.next()) {
                totalCount = rs.getInt("count");
            }

        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex2) {
            }
        }
        return totalCount;
    }

    /**
     * phoneBook에서 조건에 맞는 PhoneBookVoList를 얻는다.
     * @param dbConnection 데이터베이스 Connection
     * @return PhoneBookVoList phoneBook에서 조건에 맞는 PhoneBookVoList
     * @throws SQLException SQL문 실행 중 오류 발생 시
     */
    public PhoneBookVoList getPhoneBookVoList( Connection dbConnection, int startRow, int queryCount ) throws SQLException {
        ResultSet rs = null;
        PhoneBookVoList phoneBookVoList = null;
        try {
            // set Database Connection
            setDbConnection(dbConnection);
            // set Database Type
            setDatabaseType();
            // get sql statement
            String sql = PhoneBookSqlStore.getPhoneBookSelectSql(getDatabaseType());
            // set PrepareStatement
            setPreparedStatement(sql);
            // set parameters of Preparestatement
            this.setIntPreparedStatement(1, startRow);
            this.setIntPreparedStatement(2, queryCount);

            // execute sql
            rs = getPreparedStatement().executeQuery();
            phoneBookVoList = getPhoneBookVoListFromResultSet(rs);
        } finally {
            try {
                if (getPreparedStatement() != null) {
                    getPreparedStatement().close();
                }
            } catch (Exception ex2) {
            }
        }
        return phoneBookVoList;
    }

    private PhoneBookVoList getPhoneBookVoListFromResultSet(ResultSet resultSet) throws SQLException {
        PhoneBookVoList phoneBookVoList = new PhoneBookVoList();
        while (resultSet.next()) {
            PhoneBookVo phoneBookVo = new PhoneBookVo();

            phoneBookVo.setNumber(resultSet.getString("number"));
            phoneBookVo.setName(resultSet.getString("name"));

            phoneBookVoList.add(phoneBookVo);
        }
        return phoneBookVoList;
    }

}
